﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using TAMS;

namespace TAMS
{
    public partial class editCourses : System.Web.UI.Page
    {
        MySqlConnection conn;
        MySqlCommand sqlCommand;
        MySqlDataReader readVals;
        string strProvider = Resource1.databaseConnection;
        string type;
        string id;
        string enteredName;
        string currentYear;
        string currentSemester;


        protected void Page_Load(object sender, EventArgs e)
        {
            // Connect to the Database
            conn = new MySqlConnection(strProvider);
            conn.Open();
            string query;
            int month;

            currentYear = DateTime.Now.Year.ToString();
            month = Convert.ToInt32(DateTime.Now.Month.ToString());

            /*Dynamic Semester Parser ::G*/
            if ( month >=1 && month <= 5)
            {
                currentSemester = "SPRING";
            }
            else if (month >= 6 && month <= 9 )
            {
                currentSemester = "SUMMER";
            }
            else if (month >= 9 && month <= 12)
            {
                currentSemester = "FALL";
            }


            // Get the type
            type = Request.QueryString["type"];
            id = Request.QueryString["id"];
            enteredName = Request.QueryString["name"];

            if (!IsPostBack)
            {

                Label8.Text = "Current Semester: " + currentSemester + " " +currentYear ;

                if (type == "staff")
                {
                    string getQuery = "SELECT SC.secCorsID as 'ID',SC.cid as 'Course', SC.sectionNo as 'Section No' "
                    + "FROM SectionConsists SC, Educates E, Semester S " +
                    "WHERE E.fsid = " + id + " AND SC.secCorsId = E.secCorsId AND S.semId = SC.semId AND S.season=" + "'" + currentSemester + "'" + " AND S.year=" + "'" + currentYear + "'";

                    sqlCommand = new MySqlCommand(getQuery, conn);
                    readVals = sqlCommand.ExecuteReader();


                    if (readVals.HasRows)
                    {
                        Label6.Text = "Courses offered by, Staff ID: " + id + " and Name: " + enteredName;
                    }
                    else
                        Label6.Text = "No course offered by: " + id + " and Name: " + enteredName;

                    
                    Label1.Text = "Course information for, Staff ID: " + id + " and Name: " + enteredName;


                    GridView1.DataSource = readVals;
                    GridView1.DataBind();
                    readVals.Close();

                    string emptyQuery = "SELECT SC.secCorsID as 'ID', SC.cid as 'Course', SC.sectionNo as 'Section' "
                                        + "FROM SectionConsists SC, Semester S" +
                                        " WHERE " + " SC.secCorsId NOT IN (SELECT secCorsId FROM Educates) AND S.semId = SC.semId AND S.season=" + "'" + currentSemester + "'" + " AND S.year=" + "'" + currentYear + "'"+
                                        " GROUP BY SC.secCorsID";

                    sqlCommand = new MySqlCommand(emptyQuery, conn);
                    readVals = sqlCommand.ExecuteReader();

                    if (readVals.HasRows)
                    {
                        Label7.Text = "Courses that are not assigned to any staff. ";
                    }
                    else
                        Label7.Text = "There are no courses to add !";


                    GridView2.DataSource = readVals;                 
                    GridView2.DataBind();


                    readVals.Close();
                }

                else if (type == "student")
                {
                    string getQuery = "SELECT SC.secCorsID as 'ID',SC.cid as 'Course', SC.sectionNo as 'Section No' "
                   + "FROM SectionConsists SC, Educates E, Semester S " +
                   "WHERE E.ta_sid = " + id + " AND SC.secCorsId = E.secCorsId AND S.semId = SC.semId AND S.season=" + "'" + currentSemester + "'" + " AND S.year=" + "'" + currentYear + "'";

                    sqlCommand = new MySqlCommand(getQuery, conn);
                    readVals = sqlCommand.ExecuteReader();


                    if (readVals.HasRows)
                    {
                        Label6.Text = "Courses assisted by, TA ID: " + id + " and Name: " + enteredName;
                    }
                    else
                        Label6.Text = "No course assisted by: " + id + " and Name: " + enteredName;


                    Label1.Text = "Course information for, TA ID: " + id + " and Name: " + enteredName;


                    GridView1.DataSource = readVals;
                    GridView1.DataBind();
                    readVals.Close();

                    string emptyQuery = "SELECT SC.secCorsID as 'ID', SC.cid as 'Course', SC.sectionNo as 'Section' "
                                        + " FROM SectionConsists SC, Semester S, Educates E" +
                                        " WHERE E.ta_sid <> " + id + " AND E.secCorsId=SC.secCorsId AND S.semId = SC.semId AND S.season=" + "'" + currentSemester + "'" + " AND S.year=" + "'" + currentYear + "'" +
                                        " GROUP BY SC.secCorsId";

                    sqlCommand = new MySqlCommand(emptyQuery, conn);
                    readVals = sqlCommand.ExecuteReader();

                    if (readVals.HasRows)
                    {
                        Label7.Text = "Available courses to be a TA. ";
                    }
                    else
                        Label7.Text = "There are no courses to add !";


                    GridView2.DataSource = readVals;
                    GridView2.DataBind();


                    readVals.Close();
                }

            }
        }

        protected void add_clicked(object sender, EventArgs e)
        {
            string query;

            GridViewRow clickedRow = ((Button)sender).NamingContainer as GridViewRow;

            // id yi alıyor cell in 1. indexi
            string key = clickedRow.Cells[1].Text;

            if (type == "staff")
            {
                query = "INSERT" +
              " INTO Educates" +
              " VALUES(" + id + ", null, " + key + ")";

                sqlCommand = new MySqlCommand(query, conn);
                sqlCommand.ExecuteNonQuery();

                string url = "editCourses.aspx?" + "name=" + enteredName + "&type=staff&id=" + id;

                Response.Redirect(url);
            }
            else if (type=="gradStudent")
            {
                string getQuery = "SELECT* "
                    + "FROM SectionConsists SC, Educates E, Semester S " +
                    "WHERE E.ta_sid = " + id + " AND SC.cid =" + key + "AND SC.secCorsId = E.secCorsId AND S.semId = SC.semId AND S.season=" + "'" + currentSemester + "'" + " AND S.year=" + "'" + currentYear + "'";

                sqlCommand = new MySqlCommand(getQuery, conn);
                readVals = sqlCommand.ExecuteReader();


                if (readVals.HasRows)
                {
                    query = "UPDATE Educates" +
                        " SET ta_sid = " + id +
                        " WHERE secCorsID= " + key;

                    sqlCommand = new MySqlCommand(query, conn);
                    sqlCommand.ExecuteNonQuery();
                }
                else
                {
                    readVals.Read();
                    int staff = readVals.GetInt32("fsid");
                    query = "INSERT" +
                            " INTO Educates" +
                            " VALUES(" + staff +","+ id + "," + key + ")";

                    sqlCommand = new MySqlCommand(query, conn);
                    sqlCommand.ExecuteNonQuery();
                }
                

                string url = "editCourses.aspx?" + "name=" + enteredName + "&type=student&id=" + id;

                Response.Redirect(url);
            } 
        }

        protected void drop_clicked(object sender, EventArgs e)
        {
            string query;

            GridViewRow clickedRow = ((Button)sender).NamingContainer as GridViewRow;

            // id yi alıyor cell in 1. indexi
            string key = clickedRow.Cells[1].Text;


            if (type=="staff")
            {
                query = "DELETE" +
              " FROM Educates" +
              " WHERE fsid=" + id + " AND secCorsID=" + key;

                sqlCommand = new MySqlCommand(query, conn);
                sqlCommand.ExecuteNonQuery();

                string url = "editCourses.aspx?" + "name=" + enteredName + "&type=staff&id=" + id;

                Response.Redirect(url);
            }
            else
            {
                query = "DELETE" +
              " FROM Educates" +
              " WHERE sid=" + id + " AND secCorsID=" + key;


                string url = "editCourses.aspx?" + "name=" + enteredName + "&type=student&id=" + id;

                Response.Redirect(url);
            }

        }
    }
}